package cn.smartorigin.soas.data;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.swing.ComboBoxModel;
import javax.swing.table.DefaultTableModel;

import cn.smartorigin.soas.frame.SoasUtil;

public class TestData {
	
//	static private String SQL_QUERY_USER = "select * from UsrTbl;";
//	static private String SQL_QUERY_CLNT = "select * from ClntTbl;";
//	static private String SQL_INSERT_CLNT = "insert into ClntTbl values";
	
	static public SqliteConnecter getSqliteConnecter() throws ClassNotFoundException, SQLException{
		return new SqliteConnecter();
	}
	
	static public DefaultTableModel getUsrTblData(SqliteConnecter sqlite, DefaultTableModel  model , int month) throws SQLException{
		ResultSet rs;
		String sql = "SELECT " +
			"AccntTbl.Id," +
			"AccntTbl.Quantity, " +
			"AccntTbl.TotalPrice, " +
			"PrdctTbl.ProductName, " +
			"PrdctTbl.ProductPrice, " +
			"ClntTbl.ClientName," +
			"DeliveryTbl.DeliveryNum," +
			"DeliveryTbl.DeliverYear,"+
			"DeliveryTbl.DeliverMonth,"+
			"DeliveryTbl.DeliverDay "
			+" FROM DeliveryTbl, ClntTbl, PrdctTbl, AccntTbl WHERE " 
			+" AccntTbl.ProductId=PrdctTbl.ProductId"
			+" AND AccntTbl.ClientId=ClntTbl.ClientId"
			+" AND DeliveryTbl.DeliverMonth=" + month 
			+ "; ";
		try {
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			String time = " - ";
			while (rs.next()) {
			      Vector<Object> v=new Vector<Object>();
			      time = rs.getString("DeliverYear") + "-" + rs.getString("DeliverMonth") + "-" + rs.getString("DeliverDay");
			      v.addElement(rs.getInt("Id"));
			      v.addElement(time);
			      v.addElement(rs.getString("DeliveryNum"));
			      v.addElement(rs.getString("ProductName"));
			      v.addElement(rs.getString("Price"));
			      v.addElement(rs.getString("Quantity"));
			      v.addElement(rs.getString("TotalPrice"));
			      v.addElement(rs.getString("ClientName"));
			      model.addRow(v);
			}
			rs.close();
			sqlite.sqliteClose();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return model;
	}
	
	/**
	 * get client id by client name
	 */
	static public int getClientId(SqliteConnecter sqlite, String name){
		int id = 1;
		String sql = "SELECT ClientId FROM ClntTbl WHERE ClientName='" + name +"';";
		try {
			ResultSet rs;
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			id = rs.getInt("ClientId");
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	/**
	 * get client id by client name
	 */
	static public int getProductId(SqliteConnecter sqlite, String name){
		int id = 1;
		String sql = "SELECT ProductId FROM PrdctTbl WHERE ProductName='" + name +"';";
		try {
			ResultSet rs;
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			id = rs.getInt("ProductId");
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	/**
	 * get client id by client name
	 */
	static public int getDeliveryId(SqliteConnecter sqlite, String num){
		int id = 1;
		String sql = "SELECT DeliveryId FROM DeliveryTbl WHERE DeliveryNum='" + num +"';";
		try {
			ResultSet rs;
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			id = rs.getInt("DeliveryId");
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	static public DefaultTableModel getSelectUsrTblOrderData(SqliteConnecter sqlite, DefaultTableModel  model, int usrId, String month) throws SQLException{
		String sql = "SELECT " +
				"AccntTbl.Id," +
				"AccntTbl.Quantity, " +
				"AccntTbl.TotalPrice, " +
				"AccntTbl.UnitPrice, " +
				"PrdctTbl.ProductName, " +
				"PrdctTbl.PltKind, " +
				"ClntTbl.ClientName," +
				"PltTypeTbl.PlateTypeName, " +
				"DeliveryTbl.DeliveryNum," +
				"DeliveryTbl.DeliverYear,"+
				"DeliveryTbl.DeliverMonth,"+
				"DeliveryTbl.DeliverDay "
		+" FROM DeliveryTbl, ClntTbl, PrdctTbl, AccntTbl, PltTypeTbl WHERE " 
		+" AccntTbl.ClientId=" + usrId  
		+" AND AccntTbl.ProductId=PrdctTbl.ProductId"
		+" AND AccntTbl.DeliveryId=DeliveryTbl.DeliveryId"
		+" AND DeliveryTbl.DeliverMonth="+ month  
		+" AND ClntTbl.ClientId=" + usrId  
		+" AND PrdctTbl.PltTypeId=PltTypeTbl.Id"
		+ " ORDER BY DeliveryTbl.DeliverDay; ";
		ResultSet rs;
		try {
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			String time = " - ";
			while (rs.next()) {
			      time = rs.getString("DeliverYear") + "-" + rs.getString("DeliverMonth") + "-" + rs.getString("DeliverDay");
			      Vector<Object> v=new Vector<Object>();
			      v.addElement(rs.getInt("Id"));
			      v.addElement(time);
			      v.addElement(rs.getString("DeliveryNum"));
			      v.addElement(rs.getString("ProductName"));
			      v.addElement(rs.getString("PlateTypeName"));
			      v.addElement(rs.getString("PltKind"));
			      v.addElement(rs.getString("UnitPrice"));
			      v.addElement(rs.getString("Quantity"));
			      v.addElement(rs.getString("TotalPrice"));
			      model.addRow(v);
			}
			rs.close();
			sqlite.sqliteClose();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return model;
	}
	
	/**
	 * 
	 */
	static public Vector<Object>  getClntTblData( SqliteConnecter sqlite) throws SQLException{
		Vector<Object> v = new Vector<Object>();
		String sql = "select * from ClntTbl;";
		try {
			sqlite.sqliteConnect();
			ResultSet rs = sqlite.query(sql);
			while (rs.next()) {
				
			      
				 SoasUtil.TreeNodeData tnd = new SoasUtil.TreeNodeData();
				 tnd.treeNodeName = rs.getString("ClientName");
				 tnd.treeNodeUsrId = rs.getInt("ClientId");
			     //v.addElement(rs.getString("ClientName"));
			     //v.addElement(rs.getInt("ClientId"));
				 v.addElement(tnd);
			}
			rs.close();
			sqlite.sqliteClose();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	/**
	 * insert a new product
	 */
	static public void insertProduct(SqliteConnecter sqlite, String productName, String productPrice, String PlateTypeName, String PlateKind, int clientId){
		//int clientId = getClientId(sqlite,ClientName);
		int plateTypeInt = queryPlateTypeId(sqlite, PlateTypeName);
		String sqlvalue = "INSERT INTO PrdctTbl (ProductName, ProductPrice, PltTypeId, PltKind, ClientId) VALUES (" 
		+ "'" +productName + "',"  
		+ productPrice+","
		+ plateTypeInt+","
		+ "'" + PlateKind + "',"
		+ clientId
		+ " );";
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sqlvalue);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
	/**
	 * insert a new client
	 */
	static public void insertPlateType(SqliteConnecter sqlite, String plateName){
		String sqlvalue = "INSERT INTO PltTypeTbl (PlateTypeName) VALUES (" 
			+ "'" +plateName + "'"  
			+ " );"; 
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sqlvalue);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * insert a new client
	 */
	static public int insertClinet(SqliteConnecter sqlite, String clientName){
		int err = -1;
		String sqlvalue = "INSERT INTO ClntTbl (ClientName) VALUES (" 
			+ "'" +clientName + "'"  
			+ " );"; 
		try {
			err = queryClinet(sqlite, clientName);
			if(err == 0){
				sqlite.sqliteConnect();
				sqlite.insert(sqlvalue);
			}else{
				return err;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return err;
	}
	
	public static int queryClinet(SqliteConnecter sqlite, String clientName){
		int id = 0;
		String sql = "SELECT ClientId FROM ClntTbl " +
				"WHERE ClientName ='" + clientName +"';";
		try {
			ResultSet rs;
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			id = rs.getInt("ClientId");
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	
	/**
	 * insert a new order
	 */
	static public void insertOrder(SqliteConnecter sqlite, String clientName, String productName, String deliveryNum, String quality){
		int clientId = getClientId(sqlite,clientName);
		int productId = getProductId(sqlite,productName);
		int deliveryId = getDeliveryId(sqlite,deliveryNum);
		String sqlvalue = "INSERT INTO AccntTbl (ProductId, ClientId, DeliveryId, Quantity, TotalPrice) VALUES (" 
			+productId + ","  
			+clientId + "," 
			+deliveryId + "," 
			+quality + "," 
			+18.88 
			+ " );"; 
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sqlvalue);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	static public void insertOrder(SqliteConnecter sqlite, int clientId, String productName, String deliveryNum, String quality){
		//int clientId = getClientId(sqlite,clientName);
		int productId = getProductId(sqlite,productName);
		int deliveryId = getDeliveryId(sqlite,deliveryNum);
		float totalPrice, unitPrice;
		unitPrice = queryUnitPrice(sqlite, productId);
		totalPrice = unitPrice * Float.parseFloat(quality);;
		String sqlvalue = "INSERT INTO AccntTbl (ProductId, ClientId, DeliveryId, Quantity, TotalPrice, UnitPrice) VALUES (" 
			+productId + ","  
			+clientId + "," 
			+deliveryId + "," 
			+quality + "," 
			+totalPrice + "," 
			+unitPrice
			+ " );"; 
		try {

			sqlite.sqliteConnect();
			sqlite.insert(sqlvalue);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * query client name list
	 */
	static public  Vector<Object> queryClientList(SqliteConnecter sqlite){
		Vector<Object> v = new Vector<Object>();
		String sqlvalue = "SELECT ClientName FROM ClntTbl;"; 
		try {
			sqlite.sqliteConnect();
			ResultSet rs = sqlite.query(sqlvalue);
			while(rs.next()){
				v.addElement(rs.getString("ClientName"));
			}
			sqlite.sqliteClose();
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return v;
	}
	
	/**
	 * query delivery list num
	 */
	static public  Vector<Object> queryDeliveryList(SqliteConnecter sqlite, int clientId){
		Vector<Object> v = new Vector<Object>();
		String sql = "SELECT DISTINCT DeliveryNum  " +
		"FROM DeliveryTbl " +
		"WHERE DeliveryTbl.ClientId=" +clientId +";"; 
		
		try {
			sqlite.sqliteConnect();
			ResultSet rs = sqlite.query(sql);
			while(rs.next()){
				v.addElement(rs.getString("DeliveryNum"));
			}
			sqlite.sqliteClose();
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	static public  Vector<Object> queryDeliveryListByMonth (SqliteConnecter sqlite, int clientId, String month){
		Vector<Object> v = new Vector<Object>();
		String sql = "SELECT DISTINCT DeliveryNum  " +
		"FROM DeliveryTbl " +
		"WHERE DeliveryTbl.ClientId=" +clientId +
		" AND  DeliveryTbl.DeliverMonth=" + month+
		";"; 
		
		try {
			sqlite.sqliteConnect();
			ResultSet rs = sqlite.query(sql);
			while(rs.next()){
				v.addElement(rs.getString("DeliveryNum"));
			}
			sqlite.sqliteClose();
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	/**
	 * query delivery list num
	 */
	static public  Vector<Object> queryDeliveryList(SqliteConnecter sqlite, String clientName){
		Vector<Object> v = new Vector<Object>();
		int clientId = getClientId(sqlite,clientName);
		String sql = "SELECT DISTINCT DeliveryNum  FROM DeliveryTbl, AccntTbl " +
				"WHERE " +
				"AccntTbl.DeliveryId= DeliveryTbl.DeliveryId " +
				"AND AccntTbl.ClientId=" +clientId +";"; 
		try {
			sqlite.sqliteConnect();
			ResultSet rs = sqlite.query(sql);
			while(rs.next()){
				v.addElement(rs.getString("DeliveryNum"));
			}
			sqlite.sqliteClose();
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	/**
	 * query client product list
	 */
	static public  Vector<Object> queryProductList(SqliteConnecter sqlite, int clientId){
		Vector<Object> v = new Vector<Object>();
		//int clientId = getClientId(sqlite,clientName);
		String sqlvalue = "SELECT ProductName FROM PrdctTbl WHERE ClientId=" +clientId +";"; 
		try {
			sqlite.sqliteConnect();
			ResultSet rs = sqlite.query(sqlvalue);
			while(rs.next()){
				v.addElement(rs.getString("ProductName"));
			}
			sqlite.sqliteClose();
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	static public  Vector<Object> queryProductList(SqliteConnecter sqlite, String clientName){
		Vector<Object> v = new Vector<Object>();
		int clientId = getClientId(sqlite,clientName);
		String sqlvalue = "SELECT ProductName FROM PrdctTbl WHERE ClientId=" +clientId +";"; 
		try {
			sqlite.sqliteConnect();
			ResultSet rs = sqlite.query(sqlvalue);
			while(rs.next()){
				v.addElement(rs.getString("ProductName"));
			}
			sqlite.sqliteClose();
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	/**
	 * query client product list and product price
	 */
	static public  DefaultTableModel queryProductList(SqliteConnecter sqlite, DefaultTableModel  model, int clientId){

		String sqlvalue = "SELECT PrdctTbl.ProductName, PrdctTbl.ProductPrice, PrdctTbl.PltKind, PltTypeTbl.PlateTypeName " +
				" FROM PrdctTbl, PltTypeTbl " +
				" WHERE " +
				" PrdctTbl.ClientId =" +clientId +
				" AND PrdctTbl.PltTypeId = PltTypeTbl.Id " +
				";"; 
		try {
			sqlite.sqliteConnect();
			ResultSet rs = sqlite.query(sqlvalue);
			int i=0;
			while(rs.next()){
				Vector<Object> v = new Vector<Object>();
				v.addElement(i++);
				v.addElement(rs.getString("ProductName"));
				v.addElement(rs.getString("PlateTypeName"));
				v.addElement(rs.getString("PltKind"));
				v.addElement(rs.getString("ProductPrice"));
			    model.addRow(v);
			}
			sqlite.sqliteClose();
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return model;
	}

	public static String getClientName(SqliteConnecter sqlite, int clientId) {
		String name = null;
		String sql = "SELECT ClientName FROM ClntTbl WHERE ClientId='" + clientId +"';";
		try {
			ResultSet rs;
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			name = rs.getString("ClientName");
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return name;
	}
	
	public static void updateClientProduct(SqliteConnecter sqlite, int clientId, String productName, String oldProductName, String productPrice, String typeName, String plateKind) {
		
		int plateTypeId = queryPlateTypeId(sqlite, typeName);
		String sql = "UPDATE PrdctTbl SET ProductName = '" + productName +"', ProductPrice='" + productPrice + "', PltTypeId=" + plateTypeId +
		" , PltKind ='" + plateKind +
		"' WHERE ProductName='" + oldProductName +"' AND ClientId='" + clientId +"';";
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sql);
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void deleteClientProduct(SqliteConnecter sqlite, int clientId, String productName){
		String sql = "DELETE FROM PrdctTbl WHERE ProductName='" + productName +"' AND ClientId='" + clientId +"';";
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sql);
		}catch (SQLException e) {
			e.printStackTrace();
		}	
	}

	public static DefaultTableModel getClientList(SqliteConnecter sqlite, DefaultTableModel model) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM ClntTbl ;";
		int i = 1;
		try {
			sqlite.sqliteConnect();
			ResultSet rs  = sqlite.query(sql);
			while (rs.next()) {
				Vector<Object> v = new Vector<Object>();
				v.addElement(i++);
				v.addElement(rs.getString("ClientName"));
				model.addRow(v);
			}
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return model;
	}

	public static void deleteClient(SqliteConnecter sqlite, String clientName) {
		String sql = "DELETE FROM ClntTbl WHERE ClientName='" + clientName +"';";
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sql);
		}catch (SQLException e) {
			e.printStackTrace();
		}	
		
	}

	public static void updateClient(SqliteConnecter sqlite, String mNewClientNameStr, String mClientNameStr) {
		// TODO Auto-generated method stub
		String sql = "UPDATE ClntTbl SET ClientName = '" + mNewClientNameStr + "' WHERE ClientName='" + mClientNameStr +"';";
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sql);
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static DefaultTableModel getPlateTypeList(SqliteConnecter sqlite, DefaultTableModel model) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM PltTypeTbl ;";
		int i = 1;
		try {
			sqlite.sqliteConnect();
			ResultSet rs  = sqlite.query(sql);
			while (rs.next()) {
				Vector<Object> v = new Vector<Object>();
				v.addElement(i++);
				v.addElement(rs.getString("PlateTypeName"));
				model.addRow(v);
			}
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return model;
		
	}

	public static void deletePlateType(SqliteConnecter sqlite, String plateTypeName) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM PltTypeTbl WHERE PlateTypeName='" + plateTypeName +"';";
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sql);
		}catch (SQLException e) {
			e.printStackTrace();
		}	
	}

	public static void updatePlateType(SqliteConnecter sqlite, String mNewPlateTypeNameStr, String mPlateTypeNameStr) {
		// TODO Auto-generated method stub
		String sql = "UPDATE PltTypeTbl SET PlateTypeName = '" + mNewPlateTypeNameStr + "' WHERE PlateTypeName='" + mPlateTypeNameStr +"';";
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sql);
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static Vector<Object> queryPlateType(SqliteConnecter sqlite) {
		// TODO Auto-generated method stub
		Vector<Object> v = new Vector<Object>();
		
		String sql = "SELECT * FROM PltTypeTbl ;";
		try {
			sqlite.sqliteConnect();
			ResultSet rs = sqlite.query(sql);
			while(rs.next()){
				v.addElement(rs.getString("PlateTypeName"));
			}
			sqlite.sqliteClose();
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	public static int queryPlateTypeId(SqliteConnecter sqlite, String TypeName){
		int id = -1;
		String sql = "SELECT Id FROM PltTypeTbl WHERE PlateTypeName='" + TypeName +"';";
		try {
			ResultSet rs;
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			id = rs.getInt("Id");
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	public static int queryOrderNum(SqliteConnecter sqlite, String DeliveryNum){
		int id = -1;
		String sql = "SELECT DeliveryId FROM DeliveryTbl " +
				"WHERE DeliveryNum='" + DeliveryNum +"';";
		try {
			ResultSet rs;
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			id = rs.getInt("DeliveryId");
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	public static float queryUnitPrice(SqliteConnecter sqlite, int productId){
		float  unitPrice = -1;
		String sql = "SELECT ProductPrice FROM PrdctTbl " +
				"WHERE ProductId=" + productId +";";
		try {
			ResultSet rs;
			sqlite.sqliteConnect();
			rs = sqlite.query(sql);
			unitPrice = rs.getFloat("ProductPrice");
			rs.close();
			sqlite.sqliteClose();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return unitPrice;
	}

	public static int insertOrderNum(SqliteConnecter sqlite, int clinetId, String DeliveryNum, String DeliveryYear, String DeliveryMonth, String DeliveryDay) {
		int err = -1;
		String sqlvalue = "INSERT INTO DeliveryTbl (DeliveryNum, DeliverYear, DeliverMonth, DeliverDay, ClientId) " +
				"VALUES (" 
			+"'" + DeliveryNum + "',"  
			+"'" + DeliveryYear + "'," 
			+"'" + DeliveryMonth + "'," 
			+"'" + DeliveryDay + "'," 
			+clinetId 
			+ " );"; 
		try {
			err = queryOrderNum(sqlite, DeliveryNum);
			if(err > 0 ){
				return err;
			}
			sqlite.sqliteConnect();
			sqlite.insert(sqlvalue);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return err;
	}

	public static void deleteOrderProduct(SqliteConnecter sqlite, int id) {
		String sql = "DELETE FROM AccntTbl WHERE Id=" + id + ";";
		try {
			sqlite.sqliteConnect();
			sqlite.insert(sql);
		}catch (SQLException e) {
			e.printStackTrace();
		}	
	}
}
